from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.ext.declarative import declarative_base
import pickle
import os
import sys
from tkinter import messagebox
import tkinter


class DataBase:
    def __init__(self, db_name, username, password, port):
        self.Models = declarative_base()
        self.Engine = create_engine(f'mysql+pymysql://{username}:{password}@localhost:{port}/{db_name}')
        self.session = scoped_session(sessionmaker(bind=self.Engine))()

    def init_db(self):
        self.Models.metadata.create_all(self.Engine)

    def clear_db(self):
        self.Models.metadata.drop_all(self.Engine)

    def __del__(self):
        self.session.close()


def create_db():
    mysql_conf_file = 'mysql_conf.pkl'
    if os.path.isfile(mysql_conf_file):
        with open(mysql_conf_file, 'rb') as fin:
            db_name, db_user, db_password, db_port = pickle.load(fin)
    else:
        root = tkinter.Tk()
        root.geometry('500x300')
        db_name = tkinter.StringVar(root)
        db_user = tkinter.StringVar(root)
        db_password = tkinter.StringVar(root)
        db_port = tkinter.StringVar(root, value='3306')
        tkinter.Label(root, text='请输入mysql数据库名：', font=('microsoft yahei', 10, 'normal')).place(x=50, y=50)
        tkinter.Label(root, text='请输入mysql用户名：', font=('microsoft yahei', 10, 'normal')).place(x=50, y=80)
        tkinter.Label(root, text='请输入mysql用户密码：', font=('microsoft yahei', 10, 'normal')).place(x=50, y=110)
        tkinter.Label(root, text='请输入mysql用户端口号：', font=('microsoft yahei', 10, 'normal')).place(x=50, y=140)
        tkinter.Entry(root, textvariable=db_name, font=('microsoft yahei', 10, 'normal')).place(x=200, y=50)
        tkinter.Entry(root, textvariable=db_user, font=('microsoft yahei', 10, 'normal')).place(x=200, y=80)
        tkinter.Entry(root, textvariable=db_password, show='*', font=('microsoft yahei', 10, 'normal')).place(x=200,
                                                                                                              y=110)
        tkinter.Entry(root, textvariable=db_port, font=('microsoft yahei', 10, 'normal')).place(x=200, y=140)
        tkinter.Button(root, text='连接', font=('microsoft yahei', 10, 'normal'), command=lambda: root.destroy()).place(
            x=200, y=200)
        root.mainloop()
    try:
        db = DataBase(db_name, db_user, db_password, db_port)
    except Exception as e:
        messagebox.showerror(message=f'连接数据库出错：{e}')
        sys.exit()
    if not os.path.isfile(mysql_conf_file):
        with open(mysql_conf_file, 'wb') as fout:
            pickle.dump([db_name, db_user, db_password, db_port], fout)
    return db
